package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Serpent;
import interfaces.ISerpent;

public class CSerpent implements ISerpent {
  private CConnection db;

  public CSerpent(CConnection db) {
	this.db = db;
  }
	
  @Override
  public boolean create(Serpent serpent) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("insert into serpent (serpentid, specie, regionid, memberid) values ("+serpent.getSerpentID()+", '"+serpent.getSpecie()+"', "+ serpent.getRegionID()+ ", "+ serpent.getMemberID()+")");
    return i > 0;
  }

  @Override
  public boolean update(Serpent serpent) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("UPDATE serpent SET specie='"+ serpent.getSpecie() +"' WHERE serpentid='"+serpent.getSerpentID()+"'");
    return i > 0;
  }
  
  @Override
  public boolean delete(Serpent serpent) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("delete from serpent where serpentid='"+serpent.getSerpentID()+"'");
    return i > 0;
  }

  @Override
  public ArrayList<Serpent> retrieveByID(int serpentid) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from serpent where serpentid = " + serpentid);

    ArrayList<Serpent> serpents = new ArrayList<Serpent>();
    
    while(res.next()) {
      Serpent ser = new Serpent();
      ser.setSerpentID(Integer.parseInt(res.getString("SerpentID")));
      ser.setSpecie(res.getString("Specie"));
      ser.setRegionID(Integer.parseInt(res.getString("RegionID")));
      ser.setMemberID(Integer.parseInt(res.getString("MemberID")));
      serpents.add(ser);
    }

    return serpents;
  }

  @Override
  public ArrayList<Serpent> retrieveAll() throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from serpent");
		
    ArrayList<Serpent> serpents = new ArrayList<Serpent>();
    
    while(res.next()) {
    	Serpent ser = new Serpent();
        ser.setSerpentID(Integer.parseInt(res.getString("SerpentID")));
        ser.setSpecie(res.getString("Specie"));
        ser.setRegionID(Integer.parseInt(res.getString("RegionID")));
        ser.setMemberID(Integer.parseInt(res.getString("MemberID")));
        serpents.add(ser);
    }
    
    return serpents;
  }
}